# -*- coding: utf-8 -*-
'''
| @author: olivia.dou
| Created on: 2019/10/28 20:06
| desc:
'''
import logging,requests,traceback
import pandas as pd
import base.globalvars as glo
from base.getdata import get_header
from util.db_util import execute_query
from util.collection_util import random_select_from_list
from util.string_util import get_list_from_str
from proj_spec.DAS.basedata import get_category_name,get_type_ids



model_status_map={0:"Definition",1:"Draft",2:"Training",3:"TrainingFinished",4:"Publish"}

def __init():
    from base.get_config import get_header_file
    # 由于sphinx生成文档会执行导入部分的代码，原写在模块内的需要获取配置信息的变量会因缺少执行脚本时传入的命令行参数配置文件名而执行失败，
    # 导致无法生成导入了本模块的其他代码的文档，workround是把模块内代码移入函数内部
    global url1,url2,header1,header2
    url1 = glo.get_value('url1')
    url2 = glo.get_value('url2')
    header1 = get_header(get_header_file(), "header1")
    header2 = get_header(get_header_file(), "header2")

def get_model_element_ids(**kwargs):
    """获取模型元素id的列表
    
    :param kwargs:
        | model_name：模型名称
        | model_id：模型id
        | element_names: 元素名称列表
        | order: 排序方式，默认升序，desc 降序

    :return: 模型元素id的列表
    """
    if "model_name" in kwargs:
        model_id = get_model_id(kwargs['model_name'])
    elif "model_id" in kwargs:
        model_id = kwargs['model_id']
    else:
        logging.error("Either model_name or model_id should be specified")
        return []

    if "element_names" not in kwargs:
        condition = ""
    else:
        element_names_str = "'"+"','".join(kwargs['element_names'])+"'"
        condition = " and element_name in (%s)"%element_names_str

    if "order" in kwargs and kwargs['order']=="desc":
        condition += " order by id desc"


    sql = "SELECT id FROM t_das_model_element WHERE model_id=%s %s"%(model_id, condition)
    res = execute_query(sql)
    return [row[0] for row in res]

def get_one_element_id(model_name, **kwargs):
    """获取指定模型下的一个元素id，如kwargs包含order，则根据id排序后的结果返回，否则随机返回一个元素id
    Todo: 重构

    :param model_name: 模型名称

    :return: 元素id
    """

    element_ids = get_model_element_ids(model_name=model_name, **kwargs)
    if element_ids !=[]:
        if "order" not in kwargs: #未指定顺序，则随机获取一个
            id_list =  random_select_from_list(element_ids,n=1)
            if id_list !=[]:
                element_id = id_list[0]
            else:
                element_id = -1
        else:
            element_id = element_ids[0]
    else:
        element_id = -1

    return element_id


def get_model_element_ids_str(**kwargs):
    """获取逗号分隔的模型元素id字符串
    
    :param kwargs:
        | model_name: 模型名称
        | model_id: 模型id

    :return:  逗号分隔的模型元素id字符串
    """

    ids = get_model_element_ids(**kwargs)
    return ','.join([str(ele) for ele in ids])


# def get_model_element_id(model_name,element_name):
#     """根据模型名和元素名获取元素id
#
#     :param model_name: 模型名称
#     :param element_name: 元素名称
#
#     :return: 元素id
#     """
#
#     sql = "select e.id from t_das_model_element e inner join t_das_model m on e.model_id=m.id and m.model_name='%s' " \
#           "and e.element_name='%s'"%(model_name,element_name)
#     res = execute_query(sql)
#     try:
#         return res[0][0]
#     except Exception as e:
#         return None


def get_model_info(**kwargs):
    """获取模型基本信息

    :param kwargs:
        | model_name：模型名称
        | model_id：模型id

    :return: 模型基本信息的字典
    """
    if 'model_name' in kwargs:
        model_name = kwargs['model_name']
        model_id = get_model_id(kwargs['model_name'])
    elif "model_id" in kwargs:
        model_id = kwargs['model_id']
        model_name = get_model_name(model_id)
    else:
        logging.error("get_model_info failed:no model_name/model_id specified")
        return None
    # sql = "select id,model_name,tag_ids,markets,sectors,status,active_status,message,group_name,qcgroup,cru,crt,upu,upt " \
    #       "from t_das_model where id ='%s'"%model_id
    sql = "select * from t_das_model where id ='%s'" % model_id
    res = execute_query(sql, result_type="dict")
    if res!=[]:
        # result = pd.Series(res[0],index=["id","modelName","tagIds","markets","sectors","status","active_status","message",
        #                                  "group_name","qcgroup","cru","crt","upu","upt"])
        result = res[0]
        category_id = get_model_category_id(model_name)
        type_ids_str = get_model_type_ids_str(model_name)

        #result = result.append(pd.Series([category_id,type_ids_str], index=["categoryId","typeIds"]))
        result['category_id'] = category_id
        result['type_ids'] = type_ids_str
        return result
    else:
        return None

def get_model_id(model_name):
    """获取模型id

    :param model_name: 模型名称

    :return: 指定名称模型的id
    """
    sql = "SELECT id FROM t_das_model WHERE model_name='%s'"%model_name
    res = execute_query(sql)
    if res!=[]:
        return res[0][0]
    else:
        return -1#不存在的模型号
    # 以下会造成循环调用
    # model_info = get_model_info(model_name)
    # if model_info is not None:
    #     return model_info['id']
    # else:
    #     return -1#不存在的模型号

def get_model_status(model_name):
    """获取模型状态
    .. deprecated:: 1.0

    :param model_name: 模型名称

    :return: 模型状态：(0-定义 1-草稿 2-训练 3-训练完成 4-发布
    """

    model_info = get_model_info(model_name=model_name)
    if model_info is not None:
        return model_info['status']
    else:
        return None

def get_model_category_id(model_name):
    """获取指定模型大类id

    :param model_name: 模型名称

    :return: 指定模型的大类id
    """
    model_id = get_model_id(model_name)
    sql = "SELECT category_id FROM t_das_model_type WHERE model_id=%s"%model_id
    res = execute_query(sql)
    return res[0][0]


def get_model_type_ids(model_name, limit=-1):
    """获取指定模型的小类id列表

    :param model_name: 模型名称
    :param limit: 限制返回的小类个数，默认为-1 返回全部，否则指定需要返回的个数

    :return: 指定模型的小类id列表
    """
    model_id = get_model_id(model_name)
    if limit!=-1:
        limit_str = " LIMIT %s"%limit
    else:
        limit_str=""
    sql = "SELECT type_id FROM t_das_model_type WHERE model_id=%s %s" % (model_id, limit_str)
    res = execute_query(sql)
    return [item[0] for item in res]


def get_model_coverage_info(model_name, field_name):
    """获取指模型财年财季适用范围信息

    :param model_name: 模型名称
    :param field_name: 查询字段名称

    :return: 指定模型适用范围信息
    """
    model_id = get_model_id(model_name)
    sql = "SELECT %s FROM `t_das_model_coverage`  WHERE model_id=%s" % (field_name, model_id)
    res = execute_query(sql)
    return res[0][0]

def get_model_coverage(model_id):
    """获取指模型财年财季适用范围信息

    :param model_name: 模型名称
    :param field_name: 查询字段名称

    :return: 指定模型适用范围信息
    """
    sql = "SELECT start_fyp, end_fyp FROM `t_das_model_coverage` WHERE model_id=%s and status=0" % model_id
    res = execute_query(sql,result_type='dict')
    return res


def get_model_type_ids_str(model_name, limit=-1):
    """获取以逗号分隔的指定模型的小类id字符串

    :param model_name: 模型名称

    :return: 以逗号分隔的指定模型的小类id字符串
    """
    type_ids = get_model_type_ids(model_name)
    #return ','.join([str(id) for id in type_ids])
    return str(tuple(type_ids)).lstrip('(').rstrip(')') if len(type_ids)>1 else ('%s'%type_ids[0] if len(type_ids)==1 else "")


def get_model_tag_ids_str(model_name):
    """获取逗号分隔的指定模型的标签id字符串

    :param model_name: 模型名称

    :return: 逗号分隔的指定模型的标签id字符串
    """

    model_info = get_model_info(model_name = model_name)
    if model_info is not None:
        #return model_info['tag_ids']
        return model_info['tag_ids']
    else:
        return ""

def get_model_tags(model_name):
    """获取指定模型的标签列表

    :param model_name: 模型名称

    :return: 指定模型的标签列表
    """
    tag_ids_str = get_model_tag_ids_str(model_name)
    if tag_ids_str!="":
        tag_ids = tag_ids_str.split(',')
    else:
        tag_ids = []
    return tag_ids

def _build_time_filter(kwargs):
    if 'start_date' not in kwargs and 'end_date' not in kwargs:
        time_filter = ''
    elif 'start_date' not in kwargs and 'end_date' in kwargs and kwargs['end_date']!='':
        time_filter = " and a.publish_date <='%s'"%kwargs['end_date']
    elif 'start_date' in kwargs and kwargs['start_date']!='' and 'end_date' not in kwargs :
        time_filter = " and a.publish_date >='%s'"%kwargs['start_date']
    else:
        time_filter = " and a.publish_date between '%s' and '%s'"%(kwargs['start_date'],kwargs['end_date'])
    return time_filter


def _build_fyfp_filter(start_fyfp,end_fyfp):
    # 将report_period字段补齐两位后与report_year拼接，方便比较
    fyfp_substr = """and concat(a.report_year,
                      right(concat('00', substring(concat(a.report_year, a.report_period), 5, 6)), 2) 
                      ) 
                     """

    if start_fyfp != '' and end_fyfp != '':
        fyfp_filter = fyfp_substr + """between '%s' and '%s'""" % (start_fyfp, end_fyfp)
    elif start_fyfp == '' and end_fyfp != '':
        fyfp_filter = fyfp_substr + """<= '%s'""" % (end_fyfp)
    elif start_fyfp != '' and end_fyfp == '':
        fyfp_filter = fyfp_substr + """>= '%s'""" % (start_fyfp)
    else:  # 两者均为空
        fyfp_filter = ""

    return fyfp_filter

def _build_org_ids_str(kwargs):
    if 'org_ids_str' in kwargs:
        org_ids_str = "'" + "','".join(kwargs['org_ids_str'].split(',')) + "'"
        org_ids_str = """ and a.org_id in (%s)"""%org_ids_str
    else:
        org_ids_str = ""
    return org_ids_str

def _build_filter_from_list(input,column_name):
    if input==[] or input is None: #市场不做限制
        return ""
    else:
        return " and %s in (%s)" % (column_name,("'"+"','".join(input)+"'"))


def _filter_std_qc_status(files, kwargs):
    if 'std_qc_status' in kwargs:
        #qc_group = kwargs['qc_group']
        if 'element_ids_str' not in kwargs:
            logging.error("筛选文件失败：须指定元素id")
            return []
        # DAS-5559 维护审核任务标准化质检状态需按元素区分。 质检成功：所选元素均成功才为成功；质检失败：所选元素只要有一个失败即为失败

        # if type(kwargs['std_qc_status']) is not dict:
        #     logging.error('std_qc_status 必须是字典类型，e.g. {"status":[0,2],"match":"ALL"}')
        #     return []

        qc_status_str = "(%s)" % (','.join(str(status) for status in kwargs['std_qc_status']))


        #elif kwargs['std_qc_status'] == [1]:  # 维护任务用到
        if 'std_qc_match' not in kwargs or kwargs['std_qc_match'].lower() == 'any':  # 维护任务用到
            # DAS-3508 创建维护任务，选质检失败，不包含“不适用”
            qc_str = " and r.status in %s and element_id in (%s)" % (qc_status_str, kwargs['element_ids_str'])
            sql = """SELECT distinct f.id FROM t_das_file f INNER JOIN t_das_std_qc_element_result r ON f.id = r.file_id
                    WHERE f.id in (%s) %s
            """% (','.join(['%s'] * len(files)), qc_str)
            # args_num = 1
        else:
            # 质检成功：包含质检不适用(DAS-3466)
            qc_str = " and status in %s and element_id in (%s) group by file_id, model_id having count(*)=%s" \
                     % (qc_status_str, kwargs['element_ids_str'], len(kwargs['element_ids_str'].split(',')))
            sql = """SELECT distinct f.id FROM t_das_file f INNER JOIN t_das_std_qc_element_result r ON f.id = r.file_id
                    WHERE f.id in (%s) %s
            """ % (','.join(['%s'] * len(files)), qc_str)
        # else:
        #     logging.error("get_matched_files: std_qc_success should be either True or False, actual: %s" % kwargs[
        #         'std_qc_success'])
        #     return []

        try:
            res = execute_query(sql, files)
            files = [row[0] for row in res]
            return files
        except Exception as e:
            logging.error(e)
            traceback.print_exc()
            return []
    else:
        return files

def get_matched_files(category_id, type_ids, markets, sectors, tag_ids, start_fyfp='', end_fyfp='', **kwargs):
    """获取匹配的文件，仅匹配预处理成功的文件
    | 带std_qc_status参数的同时可能带std_review_status，与task_type参数互斥

    :param category_id: 文件大类id
    :param type_ids: 文件小类id列表，不能为空列表
    :param markets: 文件所属实体的市场列表
    :param sectors: 文件所属实体的行业列表
    :param tag_ids: 文件标签列表，可为空列表
    :param start_fyfp: 开始财年财季
    :param end_fyfp: 结束财年财季
    :param kwargs:
        | start_date：开始日期，格式'YYYY-mm-dd'
        | end_date：开始日期，格式'YYYY-mm-dd'
        | org_ids_str: 实体id字符串，由实体id经','拼接
        | std_qc_status：标准化质检状态，数组类型，数组元素0 成功, 1 失败, 2 不适用 （维护审核任务上，页面质检成功选项包含质检成功及不适用两个状态）
        | std_qc_match：标准化质检状态在多模型或多元素上的匹配规则 ‘Any’（默认）:任意一个匹配std_qc_status值， ‘All’: 全部匹配std_qc_status值
        | qc_group: 质检分组（由模型决定）
        | std_review_status: 以标准化质检成功方式创建审核任务，包含隐含条件：指定的元素中至少一个未质检或质检失败
        | element_ids_str: 指定的元素
        | task_type: 任务类型 0或1， 创建标注/采样任务时需过滤掉(0:未处理/1:系统已处理/2:已处理/4:已转移)4个状态的的文件

    :return: 与指定条件相匹配的文件id的列表
    """

    time_filter = _build_time_filter(kwargs)
    fyfp_filter = _build_fyfp_filter(start_fyfp,end_fyfp)

    market_str = _build_filter_from_list(markets,"e.market")
    sectors_str = _build_filter_from_list(sectors,"e.sector")

    tag_ids = [str(id) for id in tag_ids] if tag_ids is not None else None
    tag_ids_str = _build_filter_from_list(tag_ids,"c.tag_id")

    type_ids = [str(id) for id in type_ids] if type_ids is not None else None
    type_ids_str = _build_filter_from_list(type_ids,"a.type_id")

    org_ids_str = _build_org_ids_str(kwargs)

    #获取tag_ids列表 find_in_set():https://www.cnblogs.com/mytzq/p/7090197.html
    #拆分tag_ids并转为列，参考https://blog.csdn.net/m0_37934074/article/details/78784172
    # https://zhuanlan.zhihu.com/p/66712957
    # b必须为id从1开始连续递增的表
    sql = """select distinct c.id from (select a.id,a.org_id,substring_index(substring_index(ifnull(a.tag_ids,''),',',b.id),',',-1)  #
                    as 'tag_id' from t_das_file a
                    join t_das_dict_currency b  #b表需要具有连续的数字序列（也可以用mysql.help_topic，但序列从0开始需做相应修改） 
                    on b.id <= (length(ifnull(a.tag_ids,'')) - length(replace(ifnull(a.tag_ids,''),',',''))+1) # tag个数
                    where a.category_id=%s and a.is_delete=0 %s %s %s %s) as c
                    join t_das_file_preprocess d
                    on c.id=d.file_id
					left outer join t_das_entity_info e
                    on e.org_id=c.org_id
                    where d.status=2 %s %s %s """ % (category_id, type_ids_str,fyfp_filter,org_ids_str,time_filter,
                                                                      tag_ids_str,market_str,sectors_str)


    try:
        res = execute_query(sql)
        files = [item[0] for item in res]
        if len(files) == 0:
            return []

        files = _filter_std_qc_status(files, kwargs)

        if "std_review_status" in kwargs:
            if 'element_ids_str' not in kwargs:
                logging.error("筛选文件失败：须指定元素id")
                return []
            else:
                from proj_spec.DAS.element import get_element_info
                element_ids_str = kwargs['element_ids_str']
                element_ids = element_ids_str.split(',')
                model_id = get_element_info(element_id=element_ids[0])['model_id']

            # DAS - 5981 标准化审核状态改为数组结构
            std_review_status = []
            if "UnReview" in kwargs['std_review_status']:
                std_review_status.append(0)
            if "ReviewUnPass" in kwargs['std_review_status']:
                std_review_status.append(2)
            review_status_str = "(%s)"%(','.join(str(status) for status in std_review_status))

            # DAS-3653:标准化时会插入review_status表（不存在对应记录时）

            sql =  """SELECT distinct f.id FROM t_das_file f
                        INNER JOIN t_das_std_review_status s ON s.file_id=f.id where f.id in (%s) AND model_id = %s 
                        AND element_id in (%s) AND s.status in %s 
                     """ %(','.join(['%s'] * len(files)),model_id,element_ids_str,review_status_str)

            arg_num=1

            # args = files #不能用files复制，否则extend过程中files本身也随之改变
            args = []
            for i in range(arg_num):
                args.extend(files)
            res = execute_query(sql, args)
            files = [row[0] for row in res]

        #return [row[0] for row in res]
        return list(set(files))
    except Exception as e:
        #logging.error("未获取到匹配的文件:category_id %s,type_ids %s,tag_ids %s,start_ry,\n%s"%(category_id,type_ids,tag_ids,e))
        logging.error( "未获取到匹配的文件:%s" % e)
        return []

def get_entity_tags(model_info):
    entity_tag = get_list_from_str(model_info['entity_tag'])
    entity_tag1 = get_list_from_str(model_info['entity_tag1'])
    entity_tag2 = get_list_from_str(model_info['entity_tag2'])
    entity_tag3 = get_list_from_str(model_info['entity_tag3'])
    return [entity_tag,entity_tag1,entity_tag2,entity_tag3]


def get_model_matched_files(model_name, start_fyfp='', end_fyfp='', **kwargs):
    """
    获取匹配模型的文件，仅匹配预处理成功的文件

    :param model_name: 模型名称
    :param start_fyfp: 开始财年财季，如'2018Q4'
    :param end_fyfp: 结束财年财季，如'2018Q4'
    :param kwargs: 关键字参数，除下列参数外，其余详见get_matched_files函数对应参数
        | task_type：任务类型，标注/采样任务筛选文件指定跳过已有任务的文件时使用
        | skip_files_in_task: 是否跳过已有任务的文件，默认为True
        | skip_extract_step: 是否跳过提取步骤（提取任务使用）
        | restate_start_time: 重述开始时间
        | restate_end_time: 重述结束时间


    :return: 与指定模型在指定发布时间范围内匹配的文件id列表
    """

    model_info = get_model_info(model_name=model_name)
    model_id = model_info['id']

    if 'element_names' in kwargs:
        element_names = kwargs['element_names']
        element_ids_str = get_model_element_ids_str(model_name=model_name,element_names=element_names)
        kwargs['element_ids_str'] = element_ids_str
    if 'element_ids_str' not in kwargs:
        element_ids = get_model_element_ids(model_name=model_name)
        element_ids_str = ','.join([str(id) for id in element_ids])
        kwargs['element_ids_str']=element_ids_str
    category_id = model_info["category_id"]
    qc_group = model_info["qcgroup"]

    if "std_qc_status" in kwargs:
        kwargs['qc_group']=qc_group
    if 'type_ids_str' in kwargs:
        type_ids = [int(id) for id in str(kwargs['type_ids_str']).split(',')]
    elif 'type_names' in kwargs:
        category_name = get_category_name(category_id)
        type_ids = get_type_ids(category_name,kwargs['type_names'])
    else:
        type_ids_str = model_info["type_ids"]
        type_ids = [int(id) for id in type_ids_str.split(',')] if type_ids_str!="" and type_ids_str is not None else []

    tag_ids = get_list_from_str(model_info["tag_ids"])
    markets = get_list_from_str(model_info["markets"])
    sectors = get_list_from_str(model_info["sectors"])

    entity_tags = get_entity_tags(model_info)
    kwargs['entity_tags']=entity_tags

    files = get_matched_files(category_id, type_ids, markets, sectors, tag_ids, start_fyfp, end_fyfp, **kwargs)

    if len(files) > 0:
    # DAS-3327 需过滤掉已创建标注/采样任务（除已取消）的文件
    # DAS-3627 标注/采样/基线采样任务支持到元素级别: 已创建过任务的文件（非取消状态）剔除逻辑需支持到元素，所选元素全部创建过任务的文件将被剔除；
        if "task_type" in kwargs and ("skip_files_in_task" not in kwargs or kwargs["skip_files_in_task"] is True):

            sql = """
                    select file_id from 
                        (select file_id,count(DISTINCT element_id) as count_elements from 
                            (select * from 
                                (select i.file_id,t.id as task_id, substring_index(substring_index(t.element_ids,',',b.id),',',-1) as element_id
                                from t_das_task t
                                join t_das_dict_currency b
                                on b.id <= (length(ifnull(t.element_ids,'')) - length(replace(ifnull(t.element_ids,''),',',''))+1)
                                inner join t_das_task_item i
                                on t.id=i.task_id
                                where i.file_id in (%s)
                                and t.task_type=%s and model_id=%s and i.status in (0,1,2,4) # 已取消的除外
                                ) t1 # 文件所有指定模型的任务列表
                            where t1.element_id in (%s)
                            ) t2 # 文件所有包含任一指定元素的任务列表
                        group by file_id) t3 # 每个文件对应任务有多少元素在指定元素中
                    where t3.count_elements=%s
                """% (','.join(['%s'] * len(files)),kwargs['task_type'], model_id, kwargs['element_ids_str'],len(kwargs['element_ids_str'].split(',')))

            try:
                to_be_excluded = execute_query(sql, files)
            except Exception as e:
                logging.error(e)
                to_be_excluded = []

            to_be_excluded = [item[0] for item in to_be_excluded]
            files = list(set(files).difference(set(to_be_excluded)))

        # DAS-4120, 提取任务以结构化及其之后的步骤为起始步骤时，需跳过未做过“提取”步骤的文件
        if 'skip_extract_step' in kwargs and kwargs['skip_extract_step'] is True:
            if 'element_ids_str' not in kwargs:
                element_ids_str = get_model_element_ids_str(model_name=model_name)
            else:
                element_ids_str = kwargs['element_ids_str']
            sql = "select distinct f.id from t_das_file f left outer join t_das_extract_data d " \
                  "on f.id=d.file_id and d.model_id = %s and d.element_id in (%s)" \
                  "where f.id in (%s) and d.id is null"%(model_id,element_ids_str,','.join(['%s'] * len(files)))
            try:
                to_be_excluded=execute_query(sql,files)
            except Exception as e:
                logging.error(e)
                to_be_excluded = []

            to_be_excluded = [item[0] for item in to_be_excluded]
            files = list(set(files).difference(set(to_be_excluded)))

        if "restate_start_time" in kwargs.keys() or "restate_end_time" in kwargs.keys():
            restate_time_filter = ""
            if "restate_start_time" in kwargs.keys():
                restate_time_filter += " and upt>='%s'"%kwargs['restate_start_time']
            if "restate_end_time" in kwargs.keys():
                restate_time_filter += " and upt<='%s'"%kwargs['restate_end_time']
            sql = """select file_id from t_das_std_restate_time t where 1=1 %s 
            and file_id not in (select file_id from t_das_restate_dashboard where restate_usage=0)
            and file_id in (%s)"""%(restate_time_filter,','.join(['%s'] * len(files)))
            try:
                res=execute_query(sql,files)
                files = [row[0] for row in res]
            except Exception as e:
                logging.error(e)
                files = []

    #print(files)
    return files

def get_model_group_matched_files(model_group_name, start_fyfp='', end_fyfp='', **kwargs):
    """获取匹配模型分组的文件（创建提取任务时用到）

    :param model_group_name: 模型分组名称
    :param start_fyfp:
    :param end_fyfp:
    :param kwargs: 同get_model_matched_files

    :return: 匹配模型分组的文件
    """
    model_ids = get_model_ids(kind='AfterTrain', group_name=model_group_name)
    model_names = [get_model_name(id) for id in model_ids]
    matched_file_lists = [get_model_matched_files(model_name,start_fyfp,end_fyfp,**kwargs) for model_name in model_names]

    matched_files = []
    for file_list in matched_file_lists:
        matched_files.extend(file_list)

    matched_files = list(set(matched_files))
    return matched_files

def get_model_random_file_ids(model_name,n=1,**kwargs):
    """获取与指定模型匹配的随机选取的指定个数的文件id列表

    :param model_name: 模型名称
    :param start_fp: 开始财年财季
    :param end_fp: 结束财年财季
    :param start_time: 开始时间
    :param end_time: 结束时间
    :param n: 指定文件个数

    :return: 与指定模型匹配的随机选取的指定个数的文件id列表
    """
    files = get_model_matched_files(model_name,**kwargs)
    if files==[]:
        logging.error("模型没有匹配的文件：%s"%model_name)
        return []
    else:
        return random_select_from_list(files,n)

# def get_model_random_file_id_str(model_name,n=1):
#     ids = get_model_random_file_id(model_name,n)
#     return [str(id) for id in ids]

def get_random_structed_file(n=1):
    """ 随机获取指定个数的结构化的文件
    因接口仅返回10条数据，因此直接查数据库获取随机文件的基数
    先获取满足条件的file_id，再从file_id中随机选择n个文件

    :n: 获取的随机文件个数

    :return: 指定个数的随机的结构化的文件
    """
    sql ="""select distinct file_id from t_das_data_status d
            inner join t_das_std_biz_element m on d.element_id = m.element_id
            inner join t_das_model_element e on d.element_id=e.id 
            where struct_status=1 and e.algo_id not in (1,2)""" # 选取系统中仍存在并且做了业务映射的元素,算法1,2已废弃
    res = execute_query(sql)
    file_ids = [item[0] for item in res]
    file_ids = random_select_from_list(file_ids,n)
    sql = """select d.model_id, d.element_id, d.file_id from t_das_data_status d
            inner join t_das_std_biz_element m
            on d.element_id = m.element_id where struct_status=1 and d.file_id in (%s)"""\
          %(','.join([str(file_id) for file_id in file_ids ]))
    res = execute_query(sql)
    # datafrm = pd.DataFrame(list(res),columns=["model_id","element_id","file_id"])
    # logging.debug(datafrm)
    # return datafrm
    logging.debug(res)
    return res


def update_model_status(model_name,update_status):
    """更新模型为指定状态

    :param model_name: 模型名称
    :param status: 模型状态 Available values : Definition, Draft, Training, TrainingFinished, Publish

    :return: 无
    """
    __init()
    # status = get_model_status(model_name)
    # if status in [1,3]:
    #     model_id = get_model_id(model_name)
    #
    #     url = glo.get_value("url1") + "/api/v1/Model/update-status/%s/training?elementIds=undefined"%model_id
    #     res = requests.put(url,headers=header2)
    #     assert res.status_code==200
    model_info = get_model_info(model_name=model_name)
    model_id = model_info['id']
    current_status = model_info['status']
    if update_status!=model_status_map[current_status]: #要更新的模型状态不是当前模型状态
        url = glo.get_value("url1") + "/api/v1/Model/update-status/%s/%s" % (model_id,update_status)
        res = requests.put(url, headers=header2)
        assert res.status_code==200


def update_model(model_info):
    """以指定的模型信息更新模型

    :param model_info: 模型信息的字典

    :return: 无
    """
    __init()
    model_id = model_info['id']
    url = url1+"/api/v1/Model/%s"%model_id
    payload = dict(model_info[['category_id','id','MESSAGE','model_name','status','tag_ids','type_ids']])
    res = requests.put(url,headers = header2,json=payload)
    assert res.status_code==200

def get_model_auto_task_id(model_id):
    """获取模型发布时的全自动任务id

    :param model_id: 模型id

    :return: 模型发布时的全自动任务id
    """

    sql = """select id from t_das_auto_task where model_ids =%s and auto_mode=0"""%model_id
    res = execute_query(sql)
    assert len(res)==1,"模型%s匹配的全自动任务共%s个"%(model_id, len(res))
    try:
        return res[0][0]
    except Exception as e:
        logging.error(e)
        traceback.print_exc()
        return None


def get_model_ids(**kwargs):
    """获取指定条件的模型id

    :param kwargs:
        | kind: All-所有状态, BeforeTrain-训练完成前的（草稿、训练中） ,AfterTrain-训练后的（训练结束、已发布）
        | group_name： 模型分组名称
        | active_status： 启用状态(0：启用 1：禁用)
    :return:
    """

    if not "kind" in kwargs:
        logging.error("get_model_ids: kind required")
        return []
    else:
        kind = kwargs['kind']
        if kind=='All':
            kind_sub = ""

        elif kind=='BeforeTrain':
            kind_sub = "status in (1,2)"
            pass
        elif kind=='AfterTrain':
            kind_sub = "status in (3,4)"
        else:
            logging.error("get_model_ids: kind not as expected: %s"%kind)
            return []

    if "group_name" in kwargs:
        group_name = kwargs['group_name']
        group_name_sub = " and group_name='%s'"%group_name
    else:
        group_name_sub = ""

    if "active_status" in kwargs:
        active_status = kwargs['active_status']
        active_status_sub = " and active_status=%s"%active_status
    else:
        active_status_sub = ""

    condition = kind_sub+group_name_sub+active_status_sub
    if condition.startswith(" and"):
        condition = condition.replace(" and","",1)
    if condition.replace(" ","")!="":
        condition = "where " + condition

    sql = "select id from t_das_model %s"%condition
    try:
        res = execute_query(sql)
        model_ids = [item[0] for item in res]
        return model_ids
    except Exception as e:
        logging.error(e)
        traceback.print_exc()
        return []

def get_model_ids_str(**kwargs):
    """获取模型id列表以逗号分隔的字符串

    :param kwargs: 同get_model_ids

    :return: 模型id列表以逗号分隔的字符串
    """
    model_ids = get_model_ids(**kwargs)
    return ','.join([str(id) for id in model_ids])


def get_model_name(model_id):
    """获取模型名称

    :param model_id: 模型id

    :return: 模型名称
    """
    sql = "select model_name from t_das_model where id=%s"%model_id
    try:
        res = execute_query(sql)
        return res[0][0]
    except Exception as e:
        logging.error(e)
        traceback.print_exc()
        return ""

def get_random_model_ids_str(n=1):
    """获取指定个数随机的模型id，以逗号分隔

    :param n: 指定模型个数

    :return: 模型id，以逗号分隔
    """
    sql = "select id from t_das_model"
    try:
        res = execute_query(sql)
        id_list = [row[0] for row in res]
        random_list = random_select_from_list(id_list,n)

    except Exception as e:
        logging.error(e)
        traceback.print_exc()
        random_list =  []

    glo.set_value("model_ids",random_list)
    result = ','.join([str(id) for id in random_list])
    glo.set_value("model_ids_str", result)
    return result

def get_model_group_names():
    """获取系统中所有模型分组名称

    :return: 系统中所有模型分组名称
    """
    sql = "select DISTINCT group_name from t_das_model where group_name is not null and group_name<>''"
    try:
        res = execute_query(sql)
        return [row[0] for row in res]
    except Exception as e:
        logging.error("get_model_group:%s"%e)
        traceback.print_exc()
        return []


def get_models_with_supplement_mark():
    """获取有补充标注的模型列表

    :return: 模型id列表
    """

    sql = """select DISTINCT model_id from t_das_mark_supplement s
            INNER JOIN t_das_model m
            on s.model_id=m.id
            where m.status in (3,4)
            and m.active_status=0"""
    res = execute_query(sql)
    ids = [item[0] for item in res]

    return ids

def get_elements_of_model_with_supplement_mark(**kwargs):
    """获取模型下有补充标注的元素

    :param kwargs:
        | model_name

    :return: 元素id列表

    """
    if "model_name" in kwargs:
        model_id = get_model_id(kwargs['model_name'])
    else:
        model_id = kwargs['model_id']

    sql = """SELECT DISTINCT element_id FROM t_das_mark_supplement WHERE model_id=%s"""%model_id

    res = execute_query(sql)
    return [item[0] for item in res]


def delete_supplement_mark(model_name, element_name=''):
    """删除模型下的补充标注，若element_name不为空字符串则仅删除指定元素下的补充标注，否则删除模型下所有元素的补充标注

    :param model_name: 模型名称
    :param element_name: 元素名称，默认为空，即不指定元素

    :return:
    """
    __init()
    from proj_spec.DAS.mark import get_mark_supplement_ids
    mark_ids = get_mark_supplement_ids(model_name, element_name)
    for id in mark_ids:
        """DELETE http://qa.dassvc.chinascope.net/base-svc/api/v1/Mark/supplement/1352"""
        res = requests.delete(url1 + "/api/v1/Mark/supplement/%s"%id, headers = header1)
        assert res.status_code==200,"删除补充标注%s失败"%id




def test():
    list = get_model_matched_files('【测试】Automation-published','200603','202012',**{'std_qc_status':{'key':'value'},'std_review_status':['ReviewUnpass'],
            'element_ids_str':'${proj_spec.DAS.model.get_model_element_ids_str(model_name=\"【测试】Automation-published\")}','type_ids_str':'${proj_spec.DAS.basedata.get_type_id("上市公司公告","年度报告全文")}'}
            )
    print(list)

if __name__=="__main__":
    get_model_matched_files('【测试】Automation-published','200603','202012',**{'std_qc_status':{'key':'value'},'std_review_status':['ReviewUnpass'],
            'element_ids_str':'${proj_spec.DAS.model.get_model_element_ids_str(model_name=\"【测试】Automation-published\")}','type_ids_str':'${proj_spec.DAS.basedata.get_type_id("上市公司公告","年度报告全文")}'}
)

